package cn.topevery.garden.dal.dao.impl;

import cn.topevery.garden.dal.dao.PlaneUseMaterialDao;
import cn.topevery.garden.entity.po.PlaneUseMaterial;
import cn.topevery.garden.entity.query.PlaneUseMaterialQuery;
import cn.topevery.garden.entity.vo.PlaneUseMaterialVO;
import cn.topevery.garden.framework.base.BaseDaoImpl;
import hw.topevery.framework.db.CommandType;
import hw.topevery.framework.db.entity.DataPager;
import hw.topevery.framework.db.entity.SqlQueryMate;
import hw.topevery.framework.db.enums.ScriptConditionEnum;
import hw.topevery.framework.entity.DbCsp;
import hw.topevery.framework.entity.KeyValue;
import hw.topevery.framework.entity.Value;
import org.springframework.stereotype.Repository;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.concurrent.atomic.AtomicReference;

/**
 * 
 *
 * @author code-generate
 * @date 2022-01-24
 */
@Repository
public class PlaneUseMaterialDaoImpl extends BaseDaoImpl<PlaneUseMaterial> implements PlaneUseMaterialDao {
    @Override
    public <P extends DbCsp> List<PlaneUseMaterial> getSearch(P para) {
        return null;
    }

    

    @Override
    public DataPager<PlaneUseMaterialVO> listPage(PlaneUseMaterialQuery query) {
        String table = "t_plane_use_material tpum";

        String columns = "tpum.*";

        int pageIndex = query.pageIndex - 1;
        int pageSize = query.pageSize;

        SqlQueryMate sqlQueryMate = new SqlQueryMate(table).setColumns(columns).setOrderBy("tpum.c_id desc");
        sqlQueryMate
                .where(true, "tpum.c_db_status", ScriptConditionEnum.Eq, 0)
        ;

        return getDataPager(sqlQueryMate, pageSize, pageIndex, PlaneUseMaterialVO.class);
    }

    @Override
    public PlaneUseMaterial getEntityByColumn(String key,String value){
        KeyValue<String> map=new KeyValue<>();
        map.key=key;
        map.value=value;
        return getEntityByColumnEqValue(map);
    }

    public List<Map<String, Object>> getMeasureSta(String planeId){
       return getMap("with total as (\n" +
               "          select sum(d.c_measure) as area,\n" +
               "                 sum(d.c_plan_amount) as plan_total,\n" +
               "                 sum(d.c_pass_amount) as passed_total,\n" +
               "                 sum(d.c_obj_amount) as obj_total,\n" +
               "                 sum(d.c_amount) as final_total,\n" +
               "                 sum(d.c_budget) as amount,\n" +
               "                 max(d.c_unit_price) as price,\n" +
               "                 d.c_material_type as mtr_id\n" +
               "          from t_plane_use_material as d\n" +
               "          where d.c_plane_id = '"+planeId +
               "'          and d.c_db_status = 0\n" +
               "          group by d.c_material_type\n" +
               "\n" +
               "        ), det as (\n" +
               "\n" +
               "          select t.*,\n" +
               "                 p.c_name as mtr_name\n" +
               "          from total as t\n" +
               "            inner join t_unitprice_manure as p\n" +
               "              on t.mtr_id = p.c_id\n" +
               "        )\n" +
               "        select d.mtr_id,\n" +
               "               d.mtr_name,\n" +
               "               d.plan_total,\n" +
               "               d.passed_total,\n" +
               "                 d.obj_total,\n" +
               "               d.final_total,\n" +
               "               d.amount,\n" +
               "               d.area\n" +
               "        from det as d;");
    }

    public List<Map<String, Object>> getSeedlingSta(String planeId){
       return getMap("WITH total AS (\n" +
               "\tSELECT\n" +
               "\t\tsum( d.c_measure ) AS area,\n" +
               "\t\tsum( d.c_plan_amount ) AS plan_total,\n" +
               "\t\tsum( d.c_pass_amount ) AS passed_total,\n" +
               "\t\tsum( d.c_obj_amount ) AS obj_total,\n" +
               "\t\tsum( d.c_amount ) AS final_total,\n" +
               "\t\tsum( d.c_budget ) AS amount,\n" +
               "\t\tmax( d.c_unit_price ) AS price,\n" +
               "\t\td.c_material_type AS mtr_id\n" +
               "\tFROM\n" +
               "\t\tt_plane_use_material AS d\n" +
               "\tWHERE\n" +
               "\t\td.c_plane_id = '"+planeId +
               "\t\t'  AND d.c_db_status = 0 \n" +
               "\t GROUP BY\n" +
               "\t\t d.c_material_type\n" +
               "\t),\n" +
               "\tdet AS (\n" +
               "\tSELECT\n" +
               "\t\tt.*,\n" +
               "\t\tp.c_name AS mtr_name,\n" +
               "\t\tp.c_size AS c_size\n" +
               "\tFROM\n" +
               "\t\ttotal AS t\n" +
               "\t\tINNER JOIN t_unitprice_sapling AS p ON t.mtr_id = p.c_id\n" +
               "\t) SELECT\n" +
               "\td.mtr_id,\n" +
               "\td.mtr_name,\n" +
               "\td.plan_total,\n" +
               "\td.passed_total,\n" +
               "\td.obj_total,\n" +
               "\td.final_total,\n" +
               "\td.amount,\n" +
               "\td.c_size,\n" +
               "\td.area\n" +
               "FROM\n" +
               "\tdet AS d");
    }


    public List<PlaneUseMaterial> getPlaneMaterials(String planeId) {
        StringBuffer sb = new StringBuffer();
        sb.append(" select m.c_point_id,\n" +
                "                               m.c_point_no,\n" +
                "                               p.point_type,\n" +
                "                               m.c_point_desc,\n" +
                "                               m.c_fer_obj,\n" +
                "                               m.c_obj_amount,\n" +
                "                               CASE c.c_plane_type\n" +
                "\t                           WHEN 1 THEN (select c_name FROM t_unitprice_manure where c_id= m.c_material_type)\n" +
                "\t                           WHEN 2 THEN (select c_name FROM t_unitprice_sapling where c_id= m.c_material_type)\n" +
                "\t                           ELSE \"\"\n" +
                "                               END as material_type_name,\n" +
                "                               CASE c.c_plane_type\n" +
                "\t                           WHEN 2 THEN (SELECT c_size from t_unitprice_sapling WHERE c_id=m.c_material_type)\n" +
                "\t                           ELSE \"\"\n" +
                "\t                           END as material_size,\n" +
                "                               m.c_material_type ,\n" +
                "                               m.c_unit_price,\n" +
                "                               m.c_measure,\n" +
                "                               m.c_measure_unit,\n" +
                "                               m.c_plan_amount,\n" +
                "                               m.c_budget,\n" +
                "                               m.c_plane_use_material_guid,\n" +
                "                               m.c_pass_amount,\n" +
                "                               p.point_vol,\n" +
                "                               d.c_name as c_fer_obj_name\n" +
                "                        from t_plane_use_material as m\n" +
                "                        left join v_green_point_type_vol as p  on m.c_point_id = p.c_point_id\n" +
                "                        LEft JOIN t_plane c on m.c_plane_id=c.c_plane_id\n" +
                "                        LEFT JOIN t_sys_dictionary d on  m.c_fer_obj= d.c_id\n" +
                "                        where m.c_plane_id = ' "+planeId +"'  and m.c_db_status = 0");

        AtomicReference<List<PlaneUseMaterial>> vo = new AtomicReference<>(new ArrayList<>());
        run(sb.toString(), CommandType.Text, dbCommand -> {
            vo.set(dbCommand.executeToList(PlaneUseMaterial.class));
        });

        return vo.get();
    }

    public PlaneUseMaterial getEntityByPlaneType(Integer id, Integer type) {
        StringBuffer sb = new StringBuffer();
        Value<PlaneUseMaterial> res = new Value<>();
        sb.append(" SELECT t1.*\n" +
                "        FROM t_plane_use_material t1\n" +
                "        JOIN  t_plane t2 ON t2.c_plane_id = t1.c_plane_id\n" +
                "        where  t2.c_plane_type = '"+ type +
                "'          and t1.c_watermeterprice_id='"+id+"'");

        run(sb.toString(), CommandType.Text, dbCommand -> {
            res.data =dbCommand.executeToEntity(PlaneUseMaterial.class);
        });
        return res.data;
    }

}
